<?php 
/**
 * class Lab9_Mysql
 * mysql数据库抽象类，提供数据库操作的各类方法。实现Lab9_Database_Interface接口
 * @version 1.0
 */
class Lab9_Mysql implements Lab9_Database_interface
{
	/**
	 * 数据库连接标识
	 * @var resource
	 */
	protected $_dbConnect = NULL;
	
	/**
	 * 数据查询结果集
	 * @var resource
	 */
	protected $_result = NULL;
	
	/**
	 * 判断一个数组是否为关联数组
	 * @param Array $arr 要进行判断的数组
	 * @return Bool 关联数组返回true，索引数组返回false
	 */
	static public function isAssocArray($arr)
	{
		return array_keys($arr)!==range(0, count($arr));	
	}
	
	/**
	 * 构造函数，建立数据库连接
	 * @param String $dbHost
	 * @param String $dbName
	 * @param String $dbUser
	 * @param String $dbPwd
	 */
	public function __construct($dbHost, $dbName, $dbUser, $dbPwd)
	{
		$this->_dbConnect = @mysql_connect($dbHost, $dbUser, $dbPwd);
		if (false == $this->_dbConnect) {
			throw new Exception('数据库连接失败' . mysql_error());
		}
		if (!@mysql_select_db($dbName)) {
			throw new Exception('数据库选择失败' . mysql_error());
		}
		
		//设置字符集
		$this->_setCharset();
	}
	
	/**
	 * 析构函数，释放资源并关闭数据库连接
	 */
	public function __destruct()
	{
		//释放结果集
		$this->_freeResult();
		
		//关闭数据库连接
		if($this->_dbConnect){
			mysql_close($this->_dbConnect);
		}
	}
	
	/**
	 * MySQL中根据主键获取一条数据
	 * @see Lab9_Database_interface::selectById()
	 */
	public function selectById($tableName, $id=1, $key='id')
	{
		$sql = "SELECT * FROM `$tableName` WHERE `$key` = $id";
		
		$this->_query($sql);
		if (1 != @mysql_num_rows($this->_result)) {
			throw new Exception('记录出现重复，根据主键应仅查询出一条数据');
		}
		$row = mysql_fetch_assoc($this->_result);
		//释放结果集
		$this->_freeResult();
		return $row;
	}
	
	/**
	 * MySQL中自定义条件进行查询
	 * @see Lab9_Database_interface::select()
	 */
	public function select($tableName, Array $condition=null, $recordBegin=0, $recordLength=0, $sortCol=null, $desc=false)
	{
		$sql = "SELECT * FROM `$tableName` ";
		//构造where部分
		if ($condition) {
			$sql .= $this->_gennerateWhere($condition);
		}
		//构造order by部分
		if ($sortCol) {
			$sql .= " ORDER BY $sortCol";
		}
		if ($desc) {
			$sql .= ' DESC ';
		}
		
		//构造查询数据条数部分
		if ($recordLength != 0) {
			$sql .= " LIMIT $recordBegin,$recordLength";
		}
		else if ($recordBegin != 0) {
			$sql .= " LIMIT $recordBegin";
		}
		
		$this->_query($sql);
		//获得结果集
		$rows = $this->_fetchAll();
		//释放结果集
		$this->_freeResult();
		
		return $rows;
	}
	
	/**
	 * MySQL中获取某个表中的全部数据
	 * @see Lab9_Database_interface::selectAll()
	 */
	public function selectAll($tableName)
	{
		return $this->select($tableName);
	}
	
	/**
	 * MySQL中执行SQL语句
	 * @see Lab9_Database_interface::selectBySql()
	 */
	public function selectBySql($query)
	{
		//执行查询
		$this->_query($query);
		//获取结果集
		$rows = $this->_fetchAll();
		//释放结果集
		$this->_freeResult();
		return $rows;
	}
	
	/**
	 * MySQL中插入数据
	 * @see Lab9_Database_interface::insert()
	 */
	public function insert($tableName, $data)
	{
		//构造SQL语句
		$sql = "INSERT INTO $tableName ";
		
		//构造插入数据部分SQL语句
		$valueSql = ' (';
		$filedSql = ' (';
		foreach($data as $key=>$value){
			$filedSql .= "$key,";
			if (is_string($value) && $value!='null' && $value!='now()') {
				$valueSql .= "'$value',";
			}
			else{
				$valueSql .= "$value,";
			}
		}
		$valueSql = rtrim($valueSql, ',').')';
		$filedSql = rtrim($filedSql, ',').')';
		$sql = $sql . $filedSql . ' VALUES ' .$valueSql;
		//执行SQL语句
		$this->_query($sql);
		//$insertId = mysql_insert_id();
		//返回插入的条数
		return mysql_affected_rows();
	}
	
	/**
	 * MySQL中删除数据
	 * @see Lab9_Database_interface::delete()
	 */
	public function delete($tableName,Array $condition)
	{
		//构造SQL语句
		$sql = "DELETE FROM $tableName ";
		//构造删除条件
		$sql .= $this->_gennerateWhere($condition);
		//执行SQL语句
		$this->_query($sql);
		//返回删除的条数
		return mysql_affected_rows();
	}
	
	/**
	 * MySQL中更新数据
	 * @see Lab9_Database_interface::update()
	 */
	public function update($tableName,Array $condition,Array $newValue)
	{
		//构造SQL语句
		$sql = "UPDATE $tableName SET ";
		//设置更新值部分的SQL语句
		if (!self::isAssocArray($newValue)) {
			throw new Exception("更新值条件必须为关联数组");
		}
		else {
			foreach ($newValue as $key=>$value) {
				$nv = " $key=";
				if (is_string($value) && $value!='null' && $value!='now()') {
					$nv .= "'$value',"; 
				}
				else{
					$nv .= "$value,";
				}
				$sql .= $nv;
			}
			$sql = rtrim($sql, ',');
		}
		$sql .= $this->_gennerateWhere($condition);
		//执行SQL语句
		$this->_query($sql);
		//返回更新的数据个数
		return mysql_affected_rows();
	}
	
	/**
	 * 执行SQL语句
	 * 
	 */
	private function _query($sql)
	{
		$this->_result = mysql_query($sql);
		if (false === $this->_result) {
			throw new Exception('执行SQL失败，原因：' . mysql_error());
		}
	}
	
	/**
	 * 从结果集中获取数据
	 * @return multitype:|boolean
	 */
	private function _fetchAll()
	{
		if ($this->_result) {
			$rows = array();
			while($row = mysql_fetch_assoc($this->_result)){
				$rows[] = $row;
			}
			return $row;
		}
		else{
			return false;
		}
	}
	
	/**
	 * 根据传入的条件数组，构造SQL语句的where部分
	 * @param array $condition
	 * @return string 构造出的where部分SQL语句
	 */
	private function _gennerateWhere(Array $condition)
	{
		if (!self::isAssosArray($condition)) {
			throw new Exception('设置查询条件的数组必须为关联数组');
		}
		$where = ' WHERE ';
		foreach ($condition as $key=>$value) {
			if(is_string($value)){
				$where .= "`$key`='$value' AND ";
			}
			else{
				$where .= "`$key`=$value AND ";
			}
		}
		$where = rtrim($where, "AND");
		return $where;
	}
	
	/**
	 * 设置数据库字符集
	 * @param String $charset
	 */
	private function _setCharset($charset='utf8')
	{
		if ($this->_dbConnect){
			mysql_query("SET NAMES $charset", $this->_dbConnect);
		}
	}
	
	/**
	 * 释放mysql查询结果集
	 */
	private function _freeResult()
	{
		if ($this->_result){
			mysql_free_result($this->_result);
		}
		$this->_result = null;
	}
}